const Router = require('koa-router');
const tool = require("../../tools/tool");
const router = new Router();
const {db, QueryTypes} = require("../../models/db");
const tools = require("../../tools/tool");

// 利润表
router.post('/report/profits', async ctx => {
    let tenantId = ctx.user.tenantId;
    if(!ctx.request.body.formData.zqId) return ctx.err('请选择时间')
    let zqId = ctx.request.body.formData.zqId;
    let excel = ctx.request.body.excel;
    if(!zqId) return ctx.err('还没有开户账期')
    let zq = await db.models['zq'].findOne({
        where: {
            id: zqId,
            tenantId
        }
    })
    if(!zq) return ctx.err('账期错误')
    let ym = zq.ym
    let ret = await setProfit(tenantId,ym, ctx);
    if(ret.stat) {
        if(excel) {
            return tools.doExcelOut(ctx, ret.data, null)
        }
        else {
            return ctx.suc('', {
                total: 0,
                list: ret.data
            })
        }
    }
    else {
        return ctx.err('查询失败')
    }
})
const setProfit = async (tenantId, ym, ctx) =>{
    try {
        let sql = `exec card_jz @tenantId=${tenantId},@ym='${ym}'`
        await db.query(sql, {type: QueryTypes.UPDATE})
        let lists = await db.query(`select * from profit where tenantId=${tenantId}`, {type: QueryTypes.SELECT})
        let yb = ym.substring(0, 4) + "-01-01"
        let mb = ym + "-01"
        let me = tools.getDayStart(mb,'end',0, 'MONTH')
        if (lists.length === 0) {
            return ctx.err('利润表未初始化')
        }
        let cardsql = `SELECT  sum(iif(c.direct=N'借', price1-price2, price2-price1)) amount from card
        a INNER JOIN card_info b on a.id=b.cardId
        inner join category c on b.subjectId=c.id and a.tenantId=${tenantId} and a.billState='accounted' `
        lists.map(async profit => {
            profit.ym = ym
            if (!tool.xe.isEmpty(profit.subjectCode) && !tool.xe.isEmpty(profit.direct)) {
                let scode = profit.subjectCode
                let listM = []
                let listY = []
                if (scode == "6711") {
                    listY = await db.query(cardsql + ` and a.billDate between ${yb} and ${me} and (subjectCode like '${scode}%' or subjectCode like '6710%'  )  `)
                    listM = await db.query(cardsql + ` and a.billDate between ${mb} and ${me} and (subjectCode like '${scode}%' or subjectCode like '6710%'  ) `)
                } else {
                    listY = await db.query(cardsql + ` and a.billDate between ${yb} and ${me} and subjectCode like '${scode}%' `)
                    listM = await db.query(cardsql + `  and a.billDate between ${mb} and ${me} and subjectCode like '${scode}%' `)
                }
                profit.amountM = listM.length > 0 ? listM[0].amount : 0
                profit.amountY = listY.length > 0 ? listY[0].amount : 0
            }
        })

        let a0m = tool.xe.toNumber(lists[0].amountM)
        let a1m = tool.xe.toNumber(lists[1].amountM)
        let a2m = tool.xe.toNumber(lists[2].amountM)
        let a3m = tool.xe.toNumber(lists[3].amountM)
        let a4m = tool.xe.toNumber(lists[4].amountM)
        let a5m = tool.xe.toNumber(lists[5].amountM)
        let a6m = tool.xe.toNumber(lists[6].amountM)
        let a7m = tool.xe.toNumber(lists[7].amountM)
        let a8m = tool.xe.toNumber(lists[8].amountM)
        let a9m = tool.xe.toNumber(lists[9].amountM)
        let a10m = tool.xe.toNumber(lists[10].amountM)
        let a11m = tool.xe.toNumber(lists[11].amountM)
        let a12m = tool.xe.toNumber(lists[12].amountM)
        let a13m = tool.xe.toNumber(lists[13].amountM)
        //
        let a0y = tool.xe.toNumber(lists[0].amountY)
        let a1y = tool.xe.toNumber(lists[1].amountY)
        let a2y = tool.xe.toNumber(lists[2].amountY)
        let a3y = tool.xe.toNumber(lists[3].amountY)
        let a4y = tool.xe.toNumber(lists[4].amountY)
        let a5y = tool.xe.toNumber(lists[5].amountY)
        let a6y = tool.xe.toNumber(lists[6].amountY)
        let a7y = tool.xe.toNumber(lists[7].amountY)
        let a8y = tool.xe.toNumber(lists[8].amountY)
        let a9y = tool.xe.toNumber(lists[9].amountY)
        let a10y = tool.xe.toNumber(lists[10].amountY)
        let a11y = tool.xe.toNumber(lists[11].amountY)
        let a12y = tool.xe.toNumber(lists[12].amountY)
        let a13y = tool.xe.toNumber(lists[13].amountY)
        a0m = tools.add(a1m,a2m)
        a0y =tools.add(a1y,a2y)
        lists[0].amountM = a0m
        lists[0].amountY = a0y
        // 营业利润
        a8m = tool.minus(a0m,a3m,a4m,a5m,a6m,a7m)
        a8y = tool.minus(a0y,a3y,a4y,a5y,a6y,a7y)
        lists[8].amountM = a8m
        lists[8].amountY = a8y
        // 利润总额
        a11m = tool.minus(tool.add(a8m,a9m),a10m)
        a11y = tool.minus(tool.add(a8y,a9y), a10y)
        lists[11].amountM = a11m
        lists[11].amountY = a11y
        // 净利润
        lists[13].amountM = tool.minus(a11m,a12m)
        lists[13].amountY = tool.minus(a11y,a12y)
        // 写入当前利润表
        await db.models['profitCurrent'].destroy({
            where: {
                tenantId
            }
        })
        for (let i = 0; i < lists.length; i++) {
            let it = lists[i]
            let save = {
                tenantId,
                code: it.code,
                name: it.name,
                op: it.op,
                amountM: it.amountM ? it.amountM : 0,
                amountY: it.amountY ? it.amountY : 0,
                amountM3: 0,
                isSum: it.isSum,
                disabled: it.disabled,
                space: it.space,
                click: it.click,
                isSubject: it.isSubject,
                subjectName: it.subjectName,
                subjectCode: it.subjectCode,
                direct: it.direct,
                hasChild: it.hasChild,
                ym: it.ym
            }
            await db.models['profitCurrent'].create(save,)
        }

        return ctx.suc('', lists)
    } catch (e) {
        return ctx.err(e)
    }
}
// 新利润表
router.post('/report/profit',async ctx => {
    let tenantId = ctx.user.tenantId;
    let beginDate = ctx.request.body.formData.beginDate;
    let endDate = ctx.request.body.formData.endDate;
    if(!beginDate) {
        return  ctx.err('请输入开始日期')
    }
    if(!endDate) {
        return  ctx.err('请输入结束日期')
    }
    let sql = `exec list_profit @tenantId=${tenantId},@beginDate='${beginDate}',@endDate='${endDate}' `
    let list = await db.query(sql, {type: QueryTypes.SELECT})



    return ctx.suc('', {
        total: 0,
        list
    })
})

module.exports = router;